﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Globalization;
using System.Data.SqlClient;
using System.Collections.ObjectModel;
using MockPrjTeam4.Entity;
using MockPrjTeam4.Common;

namespace MockPrjTeam4.DAO
{
    public class SupportingMaterialDAO
    {
        public static SupportingMaterial SMObject(IDataRecord read)
        {
            return new SupportingMaterial
            {
                Url = Methods.ConvertString(read["Url"].ToString()),
                Description = Methods.ConvertString(read["Description"].ToString()),
                AddedDate = Methods.ConvertDateTime(read["AddedDate"].ToString()),
                Type = Methods.ConvertString(read["Type"].ToString()),
                UserId = Methods.ConvertNumber(read["UserId"].ToString())
            };
        }
        public static SupportingMaterial GetSupportingMaterialById(int id)
        {
            SupportingMaterial sm = new SupportingMaterial();
            string query = string.Format("SELECT Url, Description, AddedDate, Type, UserId " + 
                "FROM SupportingMaterials " +
                "WHERE SupportingMaterialId=@SMId");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@SMId", id)
            };
            IDataReader read = SqlHelper.ExecuteReader(query, parameter);
            if (read.Read())
                return SMObject(read);
            else return null;
        }

        public static DataTable GetTableSupportingMaterial(bool inActive)
        {
            string query = string.Format("SELECT SupportingMaterials.*, Account " +
            "FROM SupportingMaterials " +
            "LEFT JOIN [User] ON [User].UserId = SupportingMaterials.UserId " +
            "LEFT JOIN Organisations ON SupportingMaterials.OrganisationId = Organisations.OrganisationId ");
            if (inActive == true)
            {
                DataTable dt = SqlHelper.ExecuteQuery(query);
                try
                {
                    return dt;
                }
                catch (System.Exception ex)
                {
                    return null;
                }
            }
            else
            {
                query += string.Format("WHERE SupportingMaterials.IsActive='true'");
                DataTable dt = SqlHelper.ExecuteQuery(query);
                try
                {
                    return dt;
                }
                catch (System.Exception ex)
                {
                    return null;
                }
            }
        }

        public static bool AddSupportingMaterial(SupportingMaterial sm)
        {
            string query = string.Format("INSERT INTO SupportingMaterials( " +
                "Url, Description, AddedDate, IsActive, Type, UserId, OrganisationId) " +
                "VALUES(@Url, @Description, @AddedDate, @IsActive, @Type, @UserId, @OrganisationId)");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@Url", sm.Url),
                new SqlParameter("@Description", sm.Description),
                new SqlParameter("@AddedDate", sm.AddedDate),
                new SqlParameter("@IsActive", sm.IsActive),
                new SqlParameter("@Type", sm.Type),
                new SqlParameter("@UserId", sm.UserId),
                new SqlParameter("@OrganisationId", sm.OrganisationId)
            };
            SqlHelper.ExecuteQuery(query, parameter);
            return true;
        }

        public static bool EditSupportingMaterial(SupportingMaterial sm)
        {
            string query = string.Format("UPDATE SupportingMaterials SET " +
                                        "Url=@Url, " +
                                        "Description=@Description," + 
                                        "Type=@Type");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@Url", sm.Url),
                new SqlParameter("@Description", sm.Description),
                new SqlParameter("@Type", sm.Type)
            };
            SqlHelper.ExecuteQuery(query, parameter);
            return true;
        }
    }
}